package org.devocean.groupbuying.dao.impl;

import java.text.Normalizer;
import java.text.Normalizer.Form;
import java.util.Calendar;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TemporalType;

import org.devocean.groupbuying.dao.DealDAO;
import org.devocean.groupbuying.domain.Article;
import org.devocean.groupbuying.domain.ArticlesLocalization;
import org.devocean.groupbuying.domain.Deal;
import org.devocean.groupbuying.domain.Discount;
import org.devocean.groupbuying.domain.Explored;
import org.devocean.groupbuying.domain.Formula;
import org.devocean.groupbuying.domain.Product;
import org.devocean.groupbuying.domain.PurchaseItem;
import org.springframework.stereotype.Repository;

@Repository
public class DealDAOImpl implements DealDAO {

	@PersistenceContext
	private EntityManager em;

	private void linkToDiscount(Deal deal) {

		for (Discount discount : deal.getDiscounts()) {
			discount.setDeal(deal);
		}

	}

	private void linkToArticle(Deal deal) {
		for (Article article : deal.getArticlesLocalization().getArticles()) {
			article.setArticlesLocalization(deal.getArticlesLocalization());
		}
	}

	private String slugify(String string) {
		return Normalizer.normalize(string.toLowerCase(), Form.NFD)
				.replaceAll("\\p{InCombiningDiacriticalMarks}+", "")
				.replaceAll("[^\\p{Alnum}]+", "_");
	}

	public long countDeals() {
		return em.createQuery("SELECT COUNT(d)  FROM Deal d", Long.class)
				.getSingleResult();
	}

	public List<Deal> findAllDeals() {
		return em.createQuery("select d from Deal d", Deal.class)
				.getResultList();
	}

	public List<Deal> findDealsEntries(int firstResult, int maxResults) {
		return em
				.createQuery(
						"SELECT DISTINCT d FROM Deal d "
								+ "INNER JOIN FETCH d.articlesLocalization al "
								+ "INNER JOIN FETCH al.articles a ", Deal.class)
				.setFirstResult(firstResult).setMaxResults(maxResults)
				.getResultList();
	}

	public void deleteDeal(Integer id) {
		final Deal deal = em.find(Deal.class, id);
		if (deal != null) {
			em.remove(deal);
		}

	}

	public Deal findDealWithKey(Integer dealID, String lang) {
		final Query query = em
				.createQuery("SELECT d FROM Deal d "
						+ "INNER JOIN FETCH d.product p "
						+ "INNER JOIN FETCH p.articlesLocalization pal "
						+ "INNER JOIN FETCH pal.mediaLocalizations m "
						+ "INNER JOIN FETCH pal.articles pala "
						+ "INNER JOIN FETCH d.discounts di "
						+ "INNER JOIN FETCH di.voucher v "
						+ "LEFT JOIN FETCH v.serialKeys sk "
						+ "INNER JOIN FETCH di.formula f "
						+ "INNER JOIN FETCH f.plan pl "
						+ "INNER JOIN FETCH pl.articlesLocalization plal "
						+ "INNER JOIN FETCH plal.articles plala "
						+ "WHERE d.dealID =:di AND pala.lang=:lang AND plala.lang=:lang AND sk.valid=1");
		query.setParameter("di", dealID);
		query.setParameter("lang", lang);
		return (Deal) query.getResultList().get(0);
	}

	public Deal saveDeal(Deal deal) {
		if (deal.getArticlesLocalization().getArticlesLocalizationID() == null) {
			String title = deal.getArticlesLocalization().getArticles()
					.iterator().next().getTitle();
			deal.getArticlesLocalization().setPermalink(this.slugify(title));
		}
		linkToArticle(deal);
		linkToDiscount(deal);
		em.persist(deal);
		return deal;
	}

	public Deal updateDeal(Deal deal) {
		linkToArticle(deal);
		linkToDiscount(deal);
		final Deal merged = em.merge(deal);
		return merged;
	}

	public Deal findDealById(int id) {
		return em.find(Deal.class, id);
	}

	public Deal getFeaturedDeal(String lang) {

		final Query query = em.createQuery("SELECT DISTINCT d FROM Deal d "
				+ "INNER JOIN FETCH d.product p "
				+ "INNER JOIN FETCH p.articlesLocalization pal "
				+ "INNER JOIN FETCH pal.articles pala "
				+ "INNER JOIN FETCH p.merchant me "
				+ "INNER JOIN FETCH me.articlesLocalization ma "
				+ "INNER JOIN FETCH ma.mediaLocalizations mam "
				+ "INNER JOIN FETCH d.discounts di "
				+ "INNER JOIN FETCH di.formula f "
				+ "LEFT JOIN FETCH pal.tags s " + " WHERE pala.lang=:lang "
				+ "ORDER BY d.expireDate", Deal.class);
		query.setParameter("lang", lang).setMaxResults(1);
		try {
			return (Deal) query.getResultList().get(0);
		} catch (Exception e) {
			return null;
		}

	}

	public List<Formula> getFormulasByProductId(Integer productID) {
		return em
				.createQuery(
						"SELECT f FROM Formula f INNER JOIN FETCH f.plan pl INNER JOIN FETCH pl.product p where p.productID=:productID",
						Formula.class).setParameter("productID", productID)
				.getResultList();

	}

	public Discount getDiscountByDealID(Integer dealID, Integer formulaID) {
		List<Discount> discounts = em
				.createQuery(
						"SELECT ds FROM Discount ds JOIN FETCH ds.deal dl JOIN FETCH ds.formula f LEFT JOIN FETCH ds.voucher v where dl.dealID = :dealID and f.formulaID = :formulaID ",
						Discount.class).setParameter("dealID", dealID)
				.setParameter("formulaID", formulaID).getResultList();
		if (discounts.size() > 0)
			return discounts.get(0);
		else
			return null;

	}

	public List<Deal> findAllDeals(String lang) {
		final Query query = em.createQuery("SELECT DISTINCT d FROM Deal d "
				+ "INNER JOIN FETCH d.product p "
				+ "INNER JOIN FETCH p.articlesLocalization pal "
				+ "INNER JOIN FETCH pal.mediaLocalizations m "
				+ "INNER JOIN FETCH pal.articles pala "
				+ "INNER JOIN FETCH pal.tags t " + "WHERE pala.lang=:lang",
				Deal.class);

		query.setParameter("lang", lang);
		return query.getResultList();
	}

	public List<Deal> findDealsbyTag(String lang, String tag) {
		final Query query = em.createQuery("SELECT d FROM Deal d "
				+ "INNER JOIN FETCH d.product p "
				+ "INNER JOIN FETCH p.articlesLocalization pal "
				+ "INNER JOIN FETCH pal.mediaLocalizations m "
				+ "INNER JOIN FETCH pal.articles pala "
				+ "INNER JOIN FETCH pal.tags t "
				+ "WHERE pala.lang=:lang AND t.title=:tag", Deal.class);

		query.setParameter("tag", tag);
		query.setParameter("lang", lang);
		return query.getResultList();
	}

	public Deal findDealById(Integer id, String lang) {
		final Query query = em
				.createQuery("SELECT d FROM Deal d "
						+ "INNER JOIN FETCH d.product p "
						+ "INNER JOIN FETCH p.merchant mer "
						+ "INNER JOIN FETCH p.articlesLocalization pal "
						+ "INNER JOIN FETCH d.articlesLocalization dal "
						+ "INNER JOIN FETCH pal.mediaLocalizations m "
						+ "INNER JOIN FETCH dal.mediaLocalizations dm "
						+ "INNER JOIN FETCH pal.articles pala "
						+ "INNER JOIN FETCH dal.articles dala "
						+ "INNER JOIN FETCH d.discounts di "
						+ "INNER JOIN FETCH di.formula f "
						+ "LEFT JOIN FETCH dala.comments c "
						+ "LEFT JOIN FETCH c.customer cu "

						+ "WHERE pala.lang=:lang AND d.dealID=:dealID AND c.comment=null");
		query.setParameter("lang", lang);
		query.setParameter("dealID", id);
		return (Deal) query.getResultList().get(0);
	}

	public long countDeals(String lang) {
		final Query query = em.createQuery("SELECT COUNT(d) FROM Deal d "
				+ "LEFT JOIN  d.product p "
				+ "LEFT JOIN  p.articlesLocalization pal "
				+ "LEFT JOIN  pal.articles pala " + "WHERE pala.lang=:lang"
				+ "", Long.class);
		query.setParameter("lang", lang);
		return (Long) query.getSingleResult();
	}
	public long countDeals(String lang,Integer productID) {
		final Query query = em.createQuery("SELECT COUNT(d) FROM Deal d "
				+ "LEFT JOIN  d.product p "
				+ "LEFT JOIN  p.articlesLocalization pal "
				+ "LEFT JOIN  pal.articles pala " + "WHERE pala.lang=:lang AND p.productID=:pid"
				+ "", Long.class);
		query.setParameter("lang", lang);
		query.setParameter("pid", productID);
		return (Long) query.getSingleResult();
	}

	public List<Deal> findDealsEntries(int firstResult, int maxResults,
			String lang) {
		final Query query = em.createQuery("SELECT d FROM Deal d "
				+ "LEFT JOIN FETCH d.articlesLocalization dal "
				+ "INNER JOIN dal.articles al " + "WHERE al.lang=:lang ");
		query.setParameter("lang", lang);
		query.setFirstResult(firstResult);
		query.setMaxResults(maxResults);
		List<Deal> deals = null;
		Set<Integer> dIdset = new HashSet<Integer>();
		Set<Integer> idset = new HashSet<Integer>();
		try {
			deals = query.getResultList();
			for (Deal deal : deals) {

				dIdset.add(deal.getDealID());
				
				idset.add(deal.getArticlesLocalization()
						.getArticlesLocalizationID());
			}
			try {
				em.createQuery(
						"SELECT d FROM Deal d LEFT JOIN FETCH d.discounts dis LEFT JOIN FETCH dis.voucher v LEFT JOIN FETCH dis.formula f "
								+ "WHERE  d.dealID IN (:x)", Deal.class)
						.setParameter("x", dIdset).getResultList();
				em.createQuery(
						"SELECT al FROM ArticlesLocalization al INNER JOIN FETCH al.articles a LEFT JOIN FETCH al.mediaLocalizations m "
								+ "WHERE a.lang=:lang AND al.articlesLocalizationID IN (:x)",
						ArticlesLocalization.class).setParameter("lang", lang)
						.setParameter("x", idset).getResultList();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
		return deals;

	}

	public List<Deal> findNewDealsEntries(int firstResult, int maxResults,
			String lang) {

		final Query query = em.createQuery("SELECT d FROM Deal d "
				+ "LEFT JOIN FETCH d.articlesLocalization dal "
				+ "INNER JOIN dal.articles al " + "WHERE al.lang=:lang "
				+ "ORDER BY d.startDate DESC" + "");
		query.setParameter("lang", lang);
		query.setFirstResult(firstResult);
		query.setMaxResults(maxResults);
		List<Deal> deals = null;
		Set<Integer> dIdset = new HashSet<Integer>();
		Set<Integer> idset = new HashSet<Integer>();
		try {
			deals = query.getResultList();
			for (Deal deal : deals) {

				dIdset.add(deal.getDealID());
				
				idset.add(deal.getArticlesLocalization()
						.getArticlesLocalizationID());
			}
			try {
				em.createQuery(
						"SELECT d FROM Deal d LEFT JOIN FETCH d.discounts dis LEFT JOIN FETCH dis.voucher v LEFT JOIN FETCH dis.formula f "
								+ "WHERE  d.dealID IN (:x)", Deal.class)
						.setParameter("x", dIdset).getResultList();
				em.createQuery(
						"SELECT al FROM ArticlesLocalization al INNER JOIN FETCH al.articles a LEFT JOIN FETCH al.mediaLocalizations m "
								+ "WHERE a.lang=:lang AND al.articlesLocalizationID IN (:x)",
						ArticlesLocalization.class).setParameter("lang", lang)
						.setParameter("x", idset).getResultList();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
		return deals;

	}

	public List<Deal> findPopularDealsEntries(int firstResult, int maxResults,
			String lang) {

		final Query query = em.createQuery("SELECT d FROM Deal d "
				+ "LEFT JOIN FETCH d.articlesLocalization dal "
				+ "INNER JOIN FETCH d.product p "
				+ "INNER JOIN dal.articles al " + "WHERE al.lang=:lang "
				+ "ORDER BY p.rating DESC" + "");
		query.setParameter("lang", lang);
		query.setFirstResult(firstResult);
		query.setMaxResults(maxResults);
		List<Deal> deals = null;
		Set<Integer> dIdset = new HashSet<Integer>();
		Set<Integer> idset = new HashSet<Integer>();
		try {
			deals = query.getResultList();
			for (Deal deal : deals) {

				dIdset.add(deal.getDealID());
				
				idset.add(deal.getArticlesLocalization()
						.getArticlesLocalizationID());
			}
			try {
				em.createQuery(
						"SELECT d FROM Deal d LEFT JOIN FETCH d.discounts dis LEFT JOIN FETCH dis.voucher v LEFT JOIN FETCH dis.formula f "
								+ "WHERE  d.dealID IN (:x)", Deal.class)
						.setParameter("x", dIdset).getResultList();
				em.createQuery(
						"SELECT al FROM ArticlesLocalization al INNER JOIN FETCH al.articles a LEFT JOIN FETCH al.mediaLocalizations m "
								+ "WHERE a.lang=:lang AND al.articlesLocalizationID IN (:x)",
						ArticlesLocalization.class).setParameter("lang", lang)
						.setParameter("x", idset).getResultList();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
		return deals;

	}

	public List<Deal> findEndSoonDealsEntries(int firstResult, int maxResults,
			String lang) {

		final Query query = em.createQuery("SELECT d FROM Deal d "
				+ "LEFT JOIN FETCH d.articlesLocalization dal "
				+ "INNER JOIN dal.articles al " + "WHERE al.lang=:lang "
				+ "ORDER BY d.expireDate" + "");
		query.setParameter("lang", lang);
		query.setFirstResult(firstResult);
		query.setMaxResults(maxResults);
		List<Deal> deals = null;
		Set<Integer> dIdset = new HashSet<Integer>();
		Set<Integer> idset = new HashSet<Integer>();
		try {
			deals = query.getResultList();
			for (Deal deal : deals) {

				dIdset.add(deal.getDealID());
				
				idset.add(deal.getArticlesLocalization()
						.getArticlesLocalizationID());
			}
			try {
				em.createQuery(
						"SELECT d FROM Deal d LEFT JOIN FETCH d.discounts dis LEFT JOIN FETCH dis.voucher v LEFT JOIN FETCH dis.formula f "
								+ "WHERE  d.dealID IN (:x)", Deal.class)
						.setParameter("x", dIdset).getResultList();
				em.createQuery(
						"SELECT al FROM ArticlesLocalization al INNER JOIN FETCH al.articles a LEFT JOIN FETCH al.mediaLocalizations m "
								+ "WHERE a.lang=:lang AND al.articlesLocalizationID IN (:x)",
						ArticlesLocalization.class).setParameter("lang", lang)
						.setParameter("x", idset).getResultList();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
		return deals;
	}

	public List<PurchaseItem> findPurchaseItemsByDealID(Integer DealID) {
		return em
				.createQuery(
						"SELECT p FROM PurchaseItem p "
								+ "INNER JOIN FETCH p.discount ds "
								+ "INNER JOIN FETCH ds.deal dl "
								+ "WHERE dl.dealID=:dealID", PurchaseItem.class)
				.setParameter("dealID", DealID).getResultList();

	}

	public Long expireDeals() {

		final Query query = em
				.createQuery("UPDATE Deal d SET d.status = 'expired' "
						+ "WHERE d.status <>'expired'AND d.expireDate <= :da");
		query.setParameter("da", Calendar.getInstance().getTime(),
				TemporalType.DATE);
		return (long) query.executeUpdate();

	}

	public Deal findDealByLang(Integer localizationKey,
			String localizationRequest) {
		try {
			Deal deal = em
					.createQuery(
							"select distinct d from Deal d"
									+ " LEFT JOIN FETCH d.articlesLocalization al"
									+ " LEFT JOIN FETCH al.articles a"
									+ " where d.articlesLocalization.articlesLocalizationID=:localization and a.lang=:lang",
							Deal.class)
					.setParameter("localization", localizationKey)
					.setParameter("lang", localizationRequest).getResultList()
					.get(0);
			return deal;
		} catch (Exception e) {
			return null;
		}
	}

	public Deal findDealByArticleLocalization(Integer articleLocalizationID) {
		return em
				.createQuery(
						"SELECT d  FROM Deal d where d.articlesLocalization.articlesLocalizationID=:al",
						Deal.class).setParameter("al", articleLocalizationID)
				.getSingleResult();
	}

	public Deal findDealByArticleID(Integer id) {
		try {
			System.out.println("execution de requette");
			Deal deal = em
					.createQuery(
							"select  d from Deal d"
									+ " INNER JOIN FETCH d.articlesLocalization al"
									+ " INNER JOIN FETCH al.articles a"
									+ " where a.articleID=:articleID",
							Deal.class).setParameter("articleID", id)
					.getSingleResult();
			return deal;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	public List<Deal> findDealsbyProduct(String lang, Integer productID,
			int firstResult, int maxResults) {
		final Query query = em.createQuery("SELECT d FROM Deal d "
				+ "LEFT JOIN FETCH d.articlesLocalization dal "
				+ "INNER JOIN FETCH d.product p "
				+ "INNER JOIN dal.articles al " + "WHERE al.lang=:lang AND p.productID=:pid"
				+ "" + "");
		query.setParameter("lang", lang);
		query.setParameter("pid", productID);
		query.setFirstResult(firstResult);
		query.setMaxResults(maxResults);
		List<Deal> deals = null;
		Set<Integer> dIdset = new HashSet<Integer>();
		Set<Integer> idset = new HashSet<Integer>();
		try {
			deals = query.getResultList();
			for (Deal deal : deals) {

				dIdset.add(deal.getDealID());
				
				idset.add(deal.getArticlesLocalization()
						.getArticlesLocalizationID());
			}
			try {
				em.createQuery(
						"SELECT d FROM Deal d LEFT JOIN FETCH d.discounts dis LEFT JOIN FETCH dis.voucher v LEFT JOIN FETCH dis.formula f "
								+ "WHERE  d.dealID IN (:x)", Deal.class)
						.setParameter("x", dIdset).getResultList();
				em.createQuery(
						"SELECT al FROM ArticlesLocalization al INNER JOIN FETCH al.articles a LEFT JOIN FETCH al.mediaLocalizations m "
								+ "WHERE a.lang=:lang AND al.articlesLocalizationID IN (:x)",
						ArticlesLocalization.class).setParameter("lang", lang)
						.setParameter("x", idset).getResultList();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
		return deals;
	}

}
